/*
* sop管理管理模块
*/
const express = require('express');
const router = express.Router();
// 引入数据库
const db = require("../../config/db");
// 引入时间包
const moment = require("moment");
// 引入方法库
const { toCamelCase, toUnderlineCase, verifyToken, recursionGenerateTree } = require("../../config/utils");
// 引入日志
const log = require("../log");
// 引入project模块
const project = require("./projects");
// 引入device模块
const device = require("./device");
// 引入component模块
const component = require("./components");
// 引入设备与组件关系表
const deviceComponent = require("./device_components");
// 引入工序模块
const process = require("./process");
// 引入组件与工序关系表
const componentProcess = require("./components_process");


router.use("/", project);
router.use("/", device);
router.use("/", component);
router.use("/", deviceComponent);
router.use("/", process);
router.use("/", componentProcess);


// 查询所有*sop项目*列表-连*设备*表-连*组件*表-连*工序*表
router.get("/list", async (req, res) => {
    console.log("===========查询所有*sop项目*列表接口============");
    // 获取参数
    let { name, status } = req.query;
    // 拼接sql语句 项目表
    let sql = `select * from sop_projects `;
    let sql_where = ` where 1=1`;

    if (name) {
        sql_where += ` and (name = '${name}' or name_number = '${name}')`;
    } else {
        return res.send({ code: 200, msg: "查询*sop项目*列表成功", data: [], treeData: [], total: 0 });
    }
    if (status) {
        sql_where += ` and status = '${status}'`;
    }
    if (name || status) {
        sql += sql_where;
    }

    // 查询数据库
    db.query("查询所有*sop项目*列表", sql, (err, result) => {
        if (err) {
            res.send({ code: 500, msg: "查询所有*sop项目*列表失败", data: err });
            return;
        }
        if (result.length > 0) {
            // 生成树形结构
            let treeData = []
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
                return toCamelCase(item)
            })
            result[0].parentId = 0
            result[0].ancestors = `0`
            result[0].rowKey = 1
            treeData.push({ ...result[0], children: [] })
            // 根据项目id查询设备表
            let deviceSql = `select *,project_id as parent_id from sop_device where project_id = ${result[0].id}`;
            db.query("根据项目id查询设备表", deviceSql, (err2, deviceResult) => {
                if (err2) {
                    res.send({ code: 500, msg: "根据项目id查询设备表失败", data: err2 });
                    return;
                }
                if (deviceResult.length > 0) {
                    // 变量Json数组key转成下划线第一个大写
                    deviceResult = deviceResult.map(item => {
                        // 给前端整合
                        item.ancestors = `${result[0].ancestors},${item.parent_id}`
                        // 记录设备id
                        item.device_id = item.id
                        // 避免id重复
                        item.id = `D-${item.id}`
                        // 唯一标识
                        item.rowKey = item.id
                        return toCamelCase(item)
                    })
                    // 把设备表数据添加到树形结构中
                    deviceResult.map(item => {
                        treeData[0].children.push({ ...item, children: [] })
                    })


                    // 根据设备id查询设备与组件关联表查询组件
                    let deviceComponentSql = `select * from sop_device_sop_components dc 
                   left JOIN sop_components c 
                   ON dc.component_id = c.id 
                   where dc.device_id in (${deviceResult.map(item => item.deviceId).join(',')})`;

                    db.query("根据设备id查询设备与组件关联表查询组件", deviceComponentSql, (err3, deviceComponentResult) => {
                        if (err3) {
                            res.send({ code: 500, msg: "根据设备id查询设备与组件关联表查询组件失败", data: err3 });
                            return;
                        }
                        if (deviceComponentResult.length > 0) {
                            // 变量Json数组key转成下划线第一个大写
                            deviceComponentResult = deviceComponentResult.map(item => {
                                // 返回父组件id
                                item.parent_id = `D-${item.device_id}`
                                // 给前端整合
                                item.ancestors = `${result[0].ancestors},${item.parent_id},${item.device_id}`
                                // 记录组件id
                                item.component_id = item.id
                                // 避免id重复
                                item.id = `C-${item.id}`
                                return toCamelCase(item)
                            })
                            // 把组件表数据添加到树形结构中
                            treeData[0].children.forEach(item => {
                                item.children = []
                                deviceComponentResult.map(item2 => {
                                    if (item2.parentId == item.id) {
                                        item2.rowKey = item.rowKey + item2.id
                                        item.children.push(item2)
                                    }
                                })
                            })
                            // 根据组件id查询组件与工序关联表查询操作
                            let deviceComponentOperationSql = `select * from sop_components_sop_process cp 
                           left JOIN sop_process p 
                           ON cp.process_id = p.id 
                           where cp.component_id in (${deviceComponentResult.map(item => item.componentId).join(',')})`;

                            db.query("根据组件id查询组件与工序关联表查询操作", deviceComponentOperationSql, (err4, deviceComponentOperationResult) => {
                                if (err4) {
                                    res.send({ code: 500, msg: "根据组件id查询组件与工序关联表查询操作失败", data: err4 });
                                    return;
                                }
                                if (deviceComponentOperationResult.length > 0) {
                                    // 变量Json数组key转成下划线第一个大写
                                    deviceComponentOperationResult = deviceComponentOperationResult.map(item => {
                                        // 返回父组件id
                                        item.parent_id = `C-${item.component_id}`
                                        // 给前端整合
                                        item.ancestors = `${result[0].ancestors},${item.parent_id},${item.component_id},${item.id}`
                                        // 避免id重复
                                        item.id = `P-${item.id}`
                                        return toCamelCase(item)
                                    })
                                    // 把工序表数据添加到树形结构中
                                    treeData[0].children.forEach(item => {
                                        item.children.forEach(item2 => {
                                            item2.children = []
                                            deviceComponentOperationResult.map(item3 => {
                                                if (item3.parentId == item2.id) {
                                                    let rowKey = item.rowKey + ',' + item2.id + ',' + item3.id
                                                    item2.children.push({ ...item3, rowKey })
                                                }
                                            })
                                        })
                                    })
                                    // 返回数据
                                    res.send({ code: 200, msg: "查询所有*sop项目*列表-连*设备*表-连*组件*表-连*工序*表成功", data: [...result, ...deviceResult, ...deviceComponentResult, ...deviceComponentOperationResult], treeData: treeData });
                                } else {
                                    res.send({ code: 200, msg: "查询所有*sop项目*列表-连*设备*表-连*组件*表*工序*表成功", data: [...result, ...deviceResult, ...deviceComponentResult], treeData: treeData });
                                }
                            })

                        } else {
                            res.send({ code: 200, msg: "查询所有*sop项目*列表-连*设备*表-连*组件*表成功", data: [...result, ...deviceResult], treeData: treeData });
                        }
                    })
                } else {
                    res.send({ code: 200, msg: "查询所有*sop项目*列表-连*设备*表成功", data: [...result, ...deviceResult], treeData: treeData });
                }
            })

        } else {
            res.send({ code: 200, msg: "查询所有*sop项目*列表成功", data: [], treeData: [], total: 0 });
        }
    })
})

// 终端，获取所有*sop项目*列表
router.post("/list", async (req, res) => {
    console.log("===========终端，获取*sop项目*列表接口============");
    // 获取参数
    let { name, status } = req.body;
    // 拼接sql语句 连表查询设备表
    let sql = `select p.id as project_id, 
    d.id as device_id,
    p.name as project_name,
    p.name_number as project_name_number,
    d.name as device_name,
    p.status as project_status
    from sop_projects p
    left JOIN sop_device d ON p.id = d.project_id`;
    let sql_where = ` where 1=1`;

    if (name) {
        sql_where += ` and (p.name like '%${name}%' or p.name_number like '%${name}%')`;
    }
    if (status) {
        sql_where += ` and p.status = ${status}`;
    }
    if (name || status) {
        sql += sql_where;
    }

    // 查询数据库
    db.query("查询*sop项目*列表", sql, (err, result) => {
        if (err) {
            res.send({ code: 500, msg: "终端，查询*sop项目*列表失败", data: err });
            return;
        }
        if (result.length > 0) {
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
                return toCamelCase(item)
            })
            res.send({ code: 200, msg: "终端，查询*sop项目*==根据父类id查询子类成功", data: result });
        } else {
            res.send({ code: 200, msg: "终端，查询*sop项目*==根据父类id查询子类成功", data: [], total: 0 });
        }
    })
})

// 终端，获取*sop*设备*表连*组件*表连*工序*表
router.post("/listChild", async (req, res) => {
    console.log("===========终端，获取*sop*设备*表连*组件*表连*工序*表============");
    // 获取参数
    let { device } = req.body;
    // 根据设备id查询设备与组件关联表查询组件
    let deviceComponentSql = `select * from sop_device_sop_components dc 
    left JOIN sop_components c 
    ON dc.component_id = c.id 
    where dc.device_id in ('${device}')`;

    db.query("根据设备id查询设备与组件关联表查询组件", deviceComponentSql, (err3, deviceComponentResult) => {
        if (err3) {
            res.send({ code: 500, msg: "根据设备id查询设备与组件关联表查询组件失败", data: err3 });
            return;
        }
        if (deviceComponentResult.length > 0) {
            // 变量Json数组key转成下划线第一个大写
            deviceComponentResult = deviceComponentResult.map(item => {
                // 返回父组件id
                item.parent_id = `D-${item.device_id}`
                // 给前端整合
                item.ancestors = `${item.parent_id},${item.device_id}`
                // 记录组件id
                item.component_id = item.id
                // 避免id重复
                item.id = `C-${item.id}`
                return toCamelCase(item)
            })
            // 根据组件id查询组件与工序关联表查询操作
            let deviceComponentOperationSql = `select * from sop_components_sop_process cp 
           left JOIN sop_process p 
           ON cp.process_id = p.id 
           where cp.component_id in (${deviceComponentResult.map(item => item.componentId).join(',')})`;

            db.query("根据组件id查询组件与工序关联表查询操作", deviceComponentOperationSql, (err4, deviceComponentOperationResult) => {
                if (err4) {
                    res.send({ code: 500, msg: "根据组件id查询组件与工序关联表查询操作失败", data: err4 });
                    return;
                }
                if (deviceComponentOperationResult.length > 0) {
                    // 变量Json数组key转成下划线第一个大写
                    deviceComponentOperationResult = deviceComponentOperationResult.map(item => {
                        // 返回父组件id
                        item.parent_id = `C-${item.component_id}`
                        // 给前端整合
                        item.ancestors = `${item.parent_id},${item.component_id},${item.id}`
                        // 避免id重复
                        item.id = `P-${item.id}`
                        return toCamelCase(item)
                    })
                    
                    // 返回数据
                    res.send({ code: 200, msg: "查询所有*sop项目*列表-连*设备*表-连*组件*表-连*工序*表成功", data: [  ...deviceComponentResult, ...deviceComponentOperationResult] });
                } else {
                    res.send({ code: 200, msg: "查询所有*sop项目*列表-连*设备*表-连*组件*表*工序*表成功", data: [  ...deviceComponentResult] });
                }
            })

        } else {
            res.send({ code: 200, msg: "查询所有*sop项目*列表-连*设备*表-连*组件*表成功", data: [] });
        }
    })
})



// 查询sop项目*设备*组件*工序*列表总数接口
router.get('/total', (req, res) => {
    console.log("===========查询sop项目*设备*组件*工序*列表总数接口============");
    // 查询数据库、项目总数、设备总数、组件总数，工序总数、

    const sql = `SELECT count(*) as projectTotal FROM sop_projects
                UNION all
                SELECT count(*) as deviceTotal1 FROM sop_device
                UNION all
                SELECT count(*) as proTotal1 FROM sop_components
                UNION all
                SELECT count(*) as statueSotal FROM sop_process   `;
                
    db.query("查询sop项目*设备*组件*工序*列表总数接口", sql, (err, result) => {
        if (err) {
            res.send({ code: 500, msg: '查询sop项目*设备*组件*工序*列表总数接口失败' })
            return
        }
        // 查询项目总数
        res.send({
            code: 200,
            msg: '查询sop项目*设备*组件*工序*列表总数接口成功',
            projectTotal: result[0].projectTotal,
            deviceTotal: result[1].projectTotal,
            proTotal: result[2].projectTotal,
            statueSotal: result[3].projectTotal,
        })
    })
})

// 查询*sop*工序列表接口
router.get('/proList', (req, res) => {
    console.log("===========查询*sop*工序列表接口============");
    // 查询最新创建的10条数据库
    const sql = `select * from sop_process order by create_time asc limit 10`;
    db.query("查询*sop*工序列表接口", sql, (err, result) => {
        if (err) {
            res.send({ code: 500, msg: '查询*sop*工序列表接口失败' })
            return
        }
        if (result.length > 0) {
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
                return toCamelCase(item)
            })
            res.send({ code: 200, msg: "查询*sop*工序列表接口成功", data: result });
        } else {
            res.send({ code: 500, msg: "查询*sop*工序列表接口失败" });
        }
    })
})

// 更新**多个表**接口两个id更新不一样值
router.post('/update', (req, res) => {
    console.log("===========更新**多个表**接口两个id更新不一样值============");
    let { oldDeptId, newDeptId, newOrderNum, oldOrderNum, ancestors } = req.body;
    // 截取id字段
    oldDeptId = oldDeptId.split("-")[1];
    newDeptId = newDeptId.split("-")[1];
    let deviceId = ancestors.split(',')[1].split('-')[1]
    // 判断更新那个表，更新排序值
    let sql = ''
    switch (ancestors.split(',').length) {
        case 2:
            sql = `UPDATE sop_device 
            set order_num = case
            when id = ${oldDeptId} then '${oldOrderNum}'
            when id = ${newDeptId} then '${newOrderNum}'
            else order_num end
            where id in (${oldDeptId},${newDeptId})`
            break;
        case 3:
            // 设备和组件关联表
            sql = `UPDATE sop_device_sop_components 
            set order_num = case
            when component_id = ${oldDeptId} and device_id = ${deviceId} then '${oldOrderNum}'
            when component_id = ${newDeptId} and device_id = ${deviceId} then '${newOrderNum}'
            else order_num end
            where component_id in (${oldDeptId},${newDeptId}) and device_id in (${deviceId})`
            break;
        case 4:
            // 组件和工序关联表
            sql = `UPDATE sop_components_sop_process 
            set order_num = case
            when process_id = ${oldDeptId} and component_id = ${deviceId} then '${oldOrderNum}'
            when process_id = ${newDeptId} and component_id = ${deviceId} then '${newOrderNum}'
            else order_num end
            where process_id in (${oldDeptId},${newDeptId}) and component_id in (${deviceId})`
            break;
    }
    // 更新两个id，更新排序值
    db.query("更新**多个表**接口两个id更新不一样值", sql, (err, result) => {
        if (err) {
            res.send({ code: 500, msg: '更新**多个表**接口两个id更新不一样值失败', data: err })
            return
        }
        res.send({ code: 200, msg: '更新**多个表**接口两个id更新不一样值成功' })
    })
})



module.exports = router;
